package com.ctcc.exportlist;

import com.ctcc.entity.count.ProductList;
import com.ctcc.entity.count.ProductList_;
import com.ctcc.entity.count.User;
import com.ctcc.entity.count.User_;
import com.ctcc.repository.count.ListRepository;
import com.ctcc.repository.count.UserRepository;
import org.apache.commons.codec.digest.DigestUtils;
import org.apache.commons.io.FileUtils;
import org.apache.commons.io.FilenameUtils;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.annotation.PostConstruct;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URL;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by Peanut on 2018/4/18.
 */
@Controller
public class GenerateExcel {
    @Autowired
    UserRepository userRepository;
    @Autowired
    ListRepository listRepository;
    private static final String DIR="bill";
    private static final String URL="http://106.39.231.139";
    private ExportModule getModule(String openId) throws Exception{
        //TODO build data module
        //获取当前用户信息
        User user=userRepository.findOne(new Specification<User>() {
            @Override
            public Predicate toPredicate(Root<User> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {

                return criteriaBuilder.and(criteriaBuilder.equal(root.get(User_.openId), openId));
            }
        });
        SimpleDateFormat formatter=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        ExportModule exportModule = new ExportModule();
        exportModule.setCompany("中国电信股份有限公司北京分公司");
        exportModule.setManager(user.getUserName());
        exportModule.setDate(formatter.parse(formatter.format(new Date())));
        exportModule.setTel("");

        //获取用户当前的清单表

        List<ProductList> projectlist = listRepository.findAll(new Specification<ProductList>() {
            @Override
            public Predicate toPredicate(Root<ProductList> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {

                return criteriaBuilder.and(criteriaBuilder.equal(root.get(ProductList_.openId), openId));
            }
        });
        Float allAliPrice=0.00f;
        Float allTencentPrice=0.00f;
        Float allDianXinPrice=0.00f;
        List<ExcleDate> excleDateList = new ArrayList<>();
        for(int i=0 ;i<projectlist.size();i++){

            ExcleDate excleDate=new ExcleDate();
            excleDate.setId(String.valueOf(i+1));
            excleDate.setProductName(projectlist.get(i).getProductName());
            excleDate.setProductContent(
                    projectlist.get(i).getProductTitleOneKey()+
                    projectlist.get(i).getProductTitleOne()+";"+
                    projectlist.get(i).getProductTitleTwoKey()+
                    projectlist.get(i).getProductTitleTwo()+";"+
                    projectlist.get(i).getProductTitleThreeKey()+
                    projectlist.get(i).getProductTitleThree()

            );
            excleDate.setCount(Integer.parseInt(projectlist.get(i).getProductCount()));
            excleDate.setPrice(Float.parseFloat(projectlist.get(i).getProductPriceTy()));
            //折扣晚会设置 todo something
            excleDate.setDisCount(projectlist.get(i).getDisCount());
            excleDate.setPriceOfDisCount(projectlist.get(i).getDisCount()*(Float.parseFloat(projectlist.get(i).getProductPriceTy())));
            allDianXinPrice=allDianXinPrice+projectlist.get(i).getDisCount()*(Float.parseFloat(projectlist.get(i).getProductPriceTy()));

            excleDate.setPriceOfAli(Float.parseFloat(projectlist.get(i).getProductPriceAli()));
            allAliPrice=allAliPrice+Float.parseFloat(projectlist.get(i).getProductPriceAli());
            excleDate.setPriceOfTencent(Float.parseFloat(projectlist.get(i).getProductPriceTx()));
            allTencentPrice=allTencentPrice+Float.parseFloat(projectlist.get(i).getProductPriceTx());
            excleDateList.add(excleDate);


        }
        exportModule.setAllPriceOfAli(allAliPrice);
        exportModule.setAllPriceOfTencent(allTencentPrice);
        exportModule.setAllPrice(allDianXinPrice);
        exportModule.setExcleDates(excleDateList);

        return exportModule;

    }


    private File saveExcelFile(Workbook workbook,String openid) throws IOException {
        File dir= new File(FileUtils.getUserDirectory(), DIR);
        String fileName=openid+"___"+System.currentTimeMillis();
        String fileNameEncoded=DigestUtils.md5Hex(fileName)+".xlsx";
        File target=new File(dir,fileNameEncoded);
        FileOutputStream fos = new FileOutputStream(target);
        workbook.write(fos);
        IOUtils.closeQuietly(fos);
        return target;
    }


    @PostConstruct
    void init() {
        try {
            FileUtils.forceMkdir(new File(FileUtils.getUserDirectory(), DIR));
        } catch ( IOException e) {
        }
    }

    @RequestMapping("/getbill/{openId}/")
    @ResponseBody
    public Map exportExcel(@PathVariable String openId) throws Exception,IOException {
        ExportModule exportModule = getModule(openId);
        XSSFWorkbook workbook = new XSSFWorkbook();
        generateSheet(workbook,exportModule);
        File file= saveExcelFile(workbook,openId);

        /*FileUtils.sizeOf(file);
        FilenameUtils.getExtension(file.getName());*/
        Map<String,Object> data=new HashMap<>();
        data.put("fileName","报价清单");
        data.put("fileExt","xlsx");
        Calendar calendar=Calendar.getInstance();
        calendar.setTimeInMillis(file.lastModified());
        SimpleDateFormat formatter=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        data.put("fileTime", formatter.format(calendar.getTime()));
        data.put("fileSize",FileUtils.sizeOf(file));
        data.put("fileDownloadUrl",URL+"/bill/"+FilenameUtils.getName(file.getName()));

        //TODO add into database
        return data;
    }

    @RequestMapping("/pc/getbill/{openId}/")
    @ResponseBody
    public Map exportExcelToPc(@PathVariable String openId) throws Exception,IOException {
        ExportModule exportModule = getModule(openId);
        XSSFWorkbook workbook = new XSSFWorkbook();
        generateSheet(workbook,exportModule);
        File file= saveExcelFile(workbook,openId);
        Map<String,Object> map=new HashMap<>();
        map.put("getExcle","http://106.39.231.139/bill/"+file.getName());

        return map;
    }


     XSSFSheet generateSheet(XSSFWorkbook workbook, ExportModule exportModule) {
        XSSFSheet sheet = workbook.createSheet("报价清单");

        //------------------------------------------------------
        //添加标题
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        XSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeight(18);
        cellStyle.setFont(font);
        setWholeLineText(sheet, 0, "报价清单", cellStyle);
        //------------------------------------------------------

        //宋体 12号
        XSSFFont fontHead = workbook.createFont();
        fontHead.setFontName("宋体");
        fontHead.setFontHeight(12);

        //宋体 12号加粗
        XSSFFont fontHeadBold = workbook.createFont();
        fontHeadBold.setFontName("宋体");
        fontHeadBold.setFontHeight(12);
        fontHeadBold.setBold(true);


        XSSFCellStyle cellStyleHead = workbook.createCellStyle();
        cellStyleHead.setAlignment(HorizontalAlignment.RIGHT);

        //------------------------------------------------------
        //添加单号

         SimpleDateFormat formatter2=new SimpleDateFormat("yyyyMMddHHmmss");

        cellStyleHead.setFont(fontHeadBold);
        setWholeLineText(sheet, 1, "单号:"+formatter2.format(new Date()), cellStyleHead);
        //------------------------------------------------------

        XSSFCellStyle basicStyle = workbook.createCellStyle();
        basicStyle.setFont(fontHead);

        XSSFCellStyle basicBoldStyle = workbook.createCellStyle();
        basicBoldStyle.setFont(fontHeadBold);

        //------------------------------------------------------
        //添加基础信息
        setBasicData(sheet, 2, 0, "公司：", exportModule.getCompany(), basicBoldStyle, basicStyle);
        setBasicData(sheet, 2, 5, "客户经理：", exportModule.getManager(), basicBoldStyle, basicStyle);
        setBasicData(sheet, 3, 0, "电话：", exportModule.getTel(), basicBoldStyle, basicStyle);
        setBasicData(sheet, 3, 5, "日期：", exportModule.getDate().toString(), basicBoldStyle, basicStyle);
        //------------------------------------------------------

        //------------------------------------------------------
        //添加空行
        setWholeLineText(sheet, 4, null, null);
        //------------------------------------------------------


        /*编号	产品名称	产品配置	数量	标准价	折扣	折后价格	阿里云价格	腾讯云价格*/
        //------------------------------------------------------
        //添加表格标题行
        setTableCell(sheet, 5, 0, "编号", basicBoldStyle);
        setTableCell(sheet, 5, 1, "产品名称", basicBoldStyle);
        setTableCell(sheet, 5, 2, "产品配置", basicBoldStyle);
        setTableCell(sheet, 5, 3, "数量", basicBoldStyle);
        setTableCell(sheet, 5, 4, "标准价", basicBoldStyle);
        setTableCell(sheet, 5, 5, "折扣", basicBoldStyle);
        setTableCell(sheet, 5, 6, "折后价格", basicBoldStyle);
        setTableCell(sheet, 5, 7, "阿里云价格", basicBoldStyle);
        setTableCell(sheet, 5, 8, "腾讯云价格", basicBoldStyle);
        //------------------------------------------------------

        //------------------------------------------------------
        //添加表格内容

        for (int i = 0; i < exportModule.getExcleDates().size(); i++) {
            fillTableData(sheet, 6 + i, exportModule.getExcleDates().get(i), basicStyle);
        }
        cellStyleHead.setFont(fontHead);
    setWholeLineText(sheet, 6 + exportModule.getExcleDates().size(),                            "   折后总价：  "+exportModule.getAllPrice() +
                "   阿里云总价： "+exportModule.getAllPriceOfAli()+
                "   腾讯云总价： "+exportModule.getAllPriceOfTencent(), cellStyleHead);

        //------------------------------------------------------

        //备注：
        XSSFCell noteCell = getExsitRow(sheet, 6 + exportModule.getExcleDates().size() + 1).createCell(0);
        basicStyle.setVerticalAlignment(VerticalAlignment.TOP);
        basicStyle.setAlignment(HorizontalAlignment.LEFT);
        noteCell.setCellStyle(basicStyle);
        noteCell.setCellValue("备注：");
        sheet.addMergedRegion(new CellRangeAddress(6 + exportModule.getExcleDates().size() + 1, 6 + exportModule.getExcleDates().size() + 1 + 7, 0, 8));
        return sheet;
    }

     void fillTableData(XSSFSheet sheet, int rowIndex, ExcleDate excleDate, CellStyle cellStyle) {
        setTableCell(sheet, rowIndex, 0, excleDate.getId(), cellStyle);
        setTableCell(sheet, rowIndex, 1, excleDate.getProductName(), cellStyle);
        setTableCell(sheet, rowIndex, 2, excleDate.getProductContent(), cellStyle);
        setTableCell(sheet, rowIndex, 3, excleDate.getCount(), cellStyle);
        setTableCell(sheet, rowIndex, 4, excleDate.getPrice(), cellStyle);
        setTableCell(sheet, rowIndex, 5, excleDate.getDisCount(), cellStyle);
        setTableCell(sheet, rowIndex, 6, excleDate.getPriceOfDisCount(), cellStyle);
        setTableCell(sheet, rowIndex, 7, excleDate.getPriceOfAli(), cellStyle);
        setTableCell(sheet, rowIndex, 8, excleDate.getPriceOfTencent(), cellStyle);
    }


    void setTableCell(XSSFSheet sheet, int rowIndex, int colIndex, String content, CellStyle titleStyle) {
        XSSFRow xssfRow = getExsitRow(sheet, rowIndex);
        XSSFCell xssfCell = xssfRow.createCell(colIndex);
        xssfCell.setCellValue(content);
        xssfCell.setCellStyle(titleStyle);
    }

     void setTableCell(XSSFSheet sheet, int rowIndex, int colIndex, Number content, CellStyle titleStyle) {
        XSSFRow xssfRow = getExsitRow(sheet, rowIndex);
        XSSFCell xssfCell = xssfRow.createCell(colIndex);
        xssfCell.setCellType(CellType.NUMERIC);
        xssfCell.setCellStyle(titleStyle);
        if (content == null) return;
        if (content instanceof Float) {
            xssfCell.setCellValue(content.floatValue());
        } else if (content instanceof Integer) {
            xssfCell.setCellValue(content.intValue());
        } else {
            xssfCell.setCellValue(content.doubleValue());
        }
    }


    /**
     * 添加基础信息
     *
     * @param sheet
     * @param rowIndex
     * @param colIndex
     * @param title
     * @param content
     * @param titleStyle
     * @param contentStyle
     */
     void setBasicData(XSSFSheet sheet, int rowIndex, int colIndex, String title, String content, CellStyle titleStyle, CellStyle contentStyle) {
        XSSFRow xssfRow = getExsitRow(sheet, rowIndex);
        XSSFCell companyCell = xssfRow.createCell(colIndex);
        companyCell.setCellValue(title);
        companyCell.setCellStyle(titleStyle);
        XSSFCell companyTextCell = xssfRow.createCell(colIndex + 1);
        companyTextCell.setCellValue(content);
        companyTextCell.setCellStyle(contentStyle);
        sheet.addMergedRegion(new CellRangeAddress(xssfRow.getRowNum(), xssfRow.getRowNum(), colIndex + 1, colIndex + 4));
    }

     XSSFRow getExsitRow(XSSFSheet sheet, int rowIndex) {
        XSSFRow xssfRow = sheet.getRow(rowIndex);
        if (xssfRow == null) xssfRow = sheet.createRow(rowIndex);
        return xssfRow;
    }

     void setWholeLineText(XSSFSheet sheet, int rowIndex, String text, CellStyle cellStyle) {
        XSSFCell titleCell = getExsitRow(sheet, rowIndex).createCell(0);
        if (cellStyle != null) {
            titleCell.setCellStyle(cellStyle);
        }
        if (text != null) {
            titleCell.setCellValue(text);
        }
        sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 8));
    }





}
